<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*"%>
			<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
			<p>Display the roster of class Y</p>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");
	
						String action = request.getParameter("action");
						// Check if an insertion is requested
						CachedRowSet cachedQuery = new CachedRowSetImpl();
						if (action != null && action.equals("submit")) {
							// Begin transaction
							conn.setAutoCommit(false);
							
							PreparedStatement pstmt = conn
									.prepareStatement("SELECT * FROM student, section, enroll WHERE " +
							"section.class_id = ? AND enroll.section_id = section.section_id AND enroll.student_id = student.student_id");
							pstmt.setString(1, request.getParameter("class_id")); 
							
							ResultSet rs =pstmt.executeQuery();
							cachedQuery.populate(rs);
							conn.setAutoCommit(true);
						}
			
						// Create the statement
						Statement statement = conn.createStatement();
						
						ResultSet rsClass = statement.executeQuery("SELECT * FROM class");
						CachedRowSet cachedClass = new CachedRowSetImpl();
						cachedClass.populate(rsClass);

				%>
				<table border="1">
					<tr>
						<form action="report1b.jsp" method="POST">
							<input type="hidden" value="submit" name="action">
							<th>
								<select name="class_id" style="width:200px">
								<%
								int classId = 0;
								if (request.getParameter("class_id") != null){
									classId = Integer.parseInt(request.getParameter("class_id"));
								}
								while(cachedClass.next()){
								%>
									<option value=<%=cachedClass.getInt("class_id")%> 
									<%if(classId == cachedClass.getInt("class_id")) out.println("selected = selected");%> >
									<%=cachedClass.getString("course_number")%> 
									<%=cachedClass.getString("title")%> 
									<%=cachedClass.getString("quarter")%> 
									<%=cachedClass.getString("year")%> 
									</option>
								<% 	
								}
								%>
								</select>
							</th>
							<th><input type="submit" value="submit"></th>
						</form>
					</tr>
					<tr>
						<th>Student ID</th>
						<th>Name</th>
						<th>Residency</th>
						<th>SSN</th>
						<th>Units Taken</th>
						<th>Grade Option</th>
					</tr>
					
					<%
						// Iterate over the ResultSet
						while (cachedQuery.next()) {
							
					%>
					<tr>
                        <td><%=cachedQuery.getString("student_id") %></td>
                        <td><%=cachedQuery.getString("first_name") + " " + cachedQuery.getString("middle_name") + " " + cachedQuery.getString("last_name")%></td>
						<td><%=cachedQuery.getString("residency") %></td>
						<td><%=cachedQuery.getString("ssn") %> </td>
                        <td><%=cachedQuery.getInt("units") %></td>
                        <td><%=cachedQuery.getString("grade_type") %></td>
                    </tr>
                <%
						}
                %>
				<%
					} catch (SQLException e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					} catch (Exception e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					}
				%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>